package com.cloud.control.service.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.cloud.control.service.core.ProxyManageServices;
import com.cloud.control.service.socket.entity.ValidUserEntity;


/*
 * 本Class用于返回服务器的secure用户列表，解密后放入内存！
 * 本程序通过一个TimeTask调度，每隔一段时间和远程数据库的数据进行同步
 * 这个程序部署到Amazon Cloud上，尽量减少不必要的jar包！
 * */
public class JDBCUtils {
	private static final Log log = LogFactory.getLog(JDBCUtils.class);
	private static Connection connection = null;
	private static final ProxyManageServices service=ProxyManageServices.getInstance();

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}
	/*解析出全部DB加密的用户,解密后放入全局user的HashMap*/
	public synchronized static List<String> loadEncryptedUsers() throws SQLException {
	    PreparedStatement statement = null;
	    ResultSet resultSet = null;
	    List<String> entities = new ArrayList<String>();
	    
	    try {
	        if(connection==null){
	        	connection = getConnection();
	        }
	        /**and PARAM2!=null,表示用户已经被注册使用了,用户只能注册一次!**/
	        statement = connection.prepareStatement("SELECT PARAM_ID, PARAM_VALUE,PARAM1,PARAM2 FROM QCONF where PARAM_NAME='LOGIN_USER' ");// and PARAM2!=null 
	        resultSet = statement.executeQuery();

	        while (resultSet.next()) {
	        	ValidUserEntity entity=new ValidUserEntity();
	            String userName  = resultSet.getString("PARAM_VALUE");
	            String uuid  = resultSet.getString("PARAM_ID");
	            String userInfo  = resultSet.getString("PARAM1");
	            String param2=resultSet.getString("PARAM2");//是否被注册过?
	            
	            //长度超过20的值是RSA加密的结果
	            if(userInfo!=null && userInfo.length()>20){
	            	userInfo=RSAUtils.decryptRSADB(userInfo);
	            }
	            entity.setUserInfo(userInfo);
	            String db=RSAUtils.decryptRSADB(userName);
	            entity.setUserName(db);
	            entity.setUuid(uuid);
	            entity.setParam2(param2);
	            
	            
	            //解密后的用户放入全局解密用户组,供proxy或者sender查询的时候进行对比!
	            if(db!=null){
		            service.allCertificates.put(db, entity);
		            //log.info(db+"----"+db);
	            }
	            entities.add(db);
	        }
	    } catch (Exception e) {
			e.printStackTrace();
		} finally {
	        if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {}
	        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
	    }

	    return entities;
	}

	/*代理proxy或者sender第一次注册
	 * @name: 中文的用戶名，如：台中|冰晶（用戶）|准銅（負責人）|聯係電話
	 * */
    public synchronized static int RegisterUser(String user,String name){
    	
    	ValidUserEntity entity=service.allCertificates.get(user);
    	if(entity==null || entity.getParam2().equals("X")){//用户在数据库不存在,或者已经被注册!
    		return 0;
    	}
    	String uuid=entity.getUuid();
		int rowcnt=0;
	    PreparedStatement statement = null;
	    try {
	        if(connection==null){
	        	connection = getConnection();
	        }
	        String upd="update QCONF set PARAM2='X' where PARAM_NAME='LOGIN_USER' and PARAM2<>'X' and  PARAM_ID='"+uuid+"'";
	        //兼容舊有的版本，沒有name參數！
	        if(name!=null && name.length()>0){
	        	upd =upd+"and PARAM1='"+RSAUtils.encryptRSADB(name)+"'";
	        }
	             
	        log.error(upd);
	        PreparedStatement ps = connection.prepareStatement(upd);

	        rowcnt=ps.executeUpdate();	    
	    } catch (Exception e) {
	    	rowcnt=-1;
		} finally {
	        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
	    }
		return rowcnt;
	}
	private synchronized static Connection getConnection() throws Exception {
		/** 1. 读取jar包外的属性配置文件!**/
		String propertyFilePath = System.getProperty("user.dir") + "\\config.properties";  
		log.info("jar包位置:  "+propertyFilePath);
		
		String server=PropertiesReader.getValue(propertyFilePath, "dbserver");
		String username=PropertiesReader.getValue(propertyFilePath, "dbuser");
		String password=PropertiesReader.getValue(propertyFilePath, "dbpass");
		String dbName=PropertiesReader.getValue(propertyFilePath, "dbname");
		
        String url = "jdbc:oracle:thin:"+"@"+server+":1521:";
        String driver = "oracle.jdbc.driver.OracleDriver";

        Class.forName(driver).newInstance();
        Connection conn = DriverManager.getConnection(url + dbName, username,password);

        return conn;
    }

	public synchronized static void closeConnection() {
        try {
        	if(connection!=null){
        		connection.close();
        		connection=null;
        	}
        } catch (SQLException e) {

        }
    }
}
